SQL Window Value Functions
Table of Contents
1. LEAD & LAG – Time-based comparisons
1.1 Month-over-Month sales performance
Task 1:
Analyze month-over-month performance by finding the percentage change in sales between the current and previous months based on Sales.Orders.
💡 Suggested Answers
SELECT
*,
CurrentMonthSales - PreviousMonthSales AS MoM_Change,
ROUND(
CAST((CurrentMonthSales - PreviousMonthSales) AS FLOAT)
/ PreviousMonthSales * 100, 1
) AS MoM_Perc
FROM (
SELECT
MONTH(OrderDate) AS OrderMonth,
SUM(Sales) AS CurrentMonthSales,
LAG(SUM(Sales)) OVER (ORDER BY MONTH(OrderDate)) AS PreviousMonthSales
FROM Sales.Orders
GROUP BY MONTH(OrderDate)
) AS MonthlySales;
1.2 Customer loyalty – average days between orders
Task 2:
Perform a customer loyalty analysis by ranking customers based on the average number of days between their orders. Use LEAD to look at the next order date per customer and DATEDIFF to compute the gap.
💡 Suggested Answers
SELECT
CustomerID,
AVG(DaysUntilNextOrder) AS AvgDays,
RANK() OVER (ORDER BY COALESCE(AVG(DaysUntilNextOrder), 999999)) AS RankAvg
FROM (
SELECT
OrderID,
CustomerID,
OrderDate AS CurrentOrder,
LEAD(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS NextOrder,
DATEDIFF(
day,
OrderDate,
LEAD(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate)
) AS DaysUntilNextOrder
FROM Sales.Orders
) AS CustomerOrdersWithNext
GROUP BY CustomerID;
2. FIRST_VALUE & LAST_VALUE – Extremes within a partition
2.1 Lowest & highest sales per product and difference from lowest
Task 3:
For each order in Sales.Orders, find:
- the lowest Sales value for that
ProductID - the highest Sales value for that
ProductID - the difference between the current Sales and the lowest Sales for that product
Use FIRST_VALUE and LAST_VALUE as window value functions.
💡 Suggested Answers
SELECT
OrderID,
ProductID,
Sales,
FIRST_VALUE(Sales) OVER (PARTITION BY ProductID ORDER BY Sales) AS LowestSales,
LAST_VALUE(Sales) OVER (
PARTITION BY ProductID
ORDER BY Sales
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS HighestSales,
Sales - FIRST_VALUE(Sales) OVER (PARTITION BY ProductID ORDER BY Sales) AS SalesDifference
FROM Sales.Orders;